﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using System.Collections;
using SCRUM.Core.DAO;
using SCRUM.Core.Exceptions.Database;
using System.Data.SqlClient;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Privider for comunication with the database for task
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {

        /// <summary>
        /// Creates the task.
        /// </summary>
        /// <param name="task">The task.</param>
        /// <param name="inBli">Id of BLI the task is in.</param>
        /// <returns>Created Task</returns>
        public override Task CreateTask(Task task, int inBli)
        {
            int taskID = InsertTask(task, inBli);
            return GetTaskByID(taskID);
        }

        /// <summary>
        /// Inserts the task into database
        /// </summary>
        /// <param name="task">The task.</param>
        /// <param name="inBli">The in bli.</param>
        /// <returns>identifier of newly created task</returns>
        private int InsertTask(Task task, int inBli)
        {
            try
            {
                DbParameter dbName = CreateParameter("name", task.Name);
                DbParameter dbDescription = CreateParameter("description", task.Description);
                DbParameter dbEstimation = CreateParameter("estimation", task.Estimation);
                DbParameter dbBliID = CreateParameter("bliID", inBli);
                DbParameter dbTaskStateID = CreateParameter("taskStateID", task.State.ID);
                DbParameter dbProgress = CreateParameter("progress", task.Progress);
                DbParameter dbPriority = CreateParameter("priority", task.Priority);

                connection.Open();

                int data = ExecuteNonQuery(
                    "task/CreateTask"
                    , dbName
                    , dbDescription
                    , dbEstimation
                    , dbBliID
                    , dbTaskStateID
                    , dbProgress
                    , dbPriority
                );
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("TaskNotCreated");
                }
                return Convert.ToInt32(GetScalar("task/GetLastInsertedTaskID"));
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("TaskNotCreated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Updates the task.
        /// </summary>
        /// <param name="task">The task.</param>
        /// <param name="inBli">Id of BLI the task is in.</param>
        public override void UpdateTask(Task task, int inBli)
        {
            try
            {
                DbParameter dbID = CreateParameter("taskID", task.ID);
                DbParameter dbName = CreateParameter("name", task.Name);
                DbParameter dbDescription = CreateParameter("description", task.Description);
                DbParameter dbEstimation = CreateParameter("estimation", task.Estimation);
                DbParameter dbBliID = CreateParameter("bliID", inBli);
                DbParameter dbTaskStateID = CreateParameter("taskStateID", task.State.ID);
                DbParameter dbProgress = CreateParameter("progress", task.Progress);
                DbParameter dbPriority = CreateParameter("priority", task.Priority);

                connection.Open();

                int data = ExecuteNonQuery(
                    "task/UpdateTask"
                    , dbID 
                    , dbName
                    , dbDescription
                    , dbEstimation
                    , dbBliID
                    , dbTaskStateID
                    , dbProgress
                    , dbPriority
                );
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("TaskToBeUpdatedNotFound");
                }
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("TaskCouldNotBeUpdated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes the task.
        /// </summary>
        /// <param name="taskID">The task ID.</param>
        public override void DeleteTask(int taskID)
        {
            try
            {
                DbParameter dbId = CreateParameter("taskID", taskID);

                connection.Open();

                int data = ExecuteNonQuery("task/DeleteTask", dbId);
                if (data == 0)
                {
                    throw new Exception("TaskToBeDeletedNotFound");
                }
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("TaskCouldNotBeDeleted");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Creates task object according to result from dataReader
        /// </summary>
        /// <param name="data">Data reader</param>
        /// <returns>Task</returns>
        private Task GetTask(DbDataReader data)
        {
            return new Task(
                GetIntByColumn(data, "ID"),
                GetStringByColumn(data, "NAME"),
                GetStringByColumn(data, "DESCRIPTION"),
                GetDoubleByColumn(data, "ESTIMATION"),
                GetIntByColumn(data, "TASK_STATE_ID"),
                GetDoubleByColumn(data, "PROGRESS"),
                GetIntByColumn(data, "PRIORITY"),
                GetDateByColumn(data, "CHANGED"),
                GetIntByColumn(data, "BLI_ID")
            );
        }

        public override IList<Task> GetAllTasks(int projectID, int releaseID, int sprintID, int userStoryID, int bliID, int taskID)
        {
            try
            {
                IList<Task> tasks = new List<Task>();

                DbParameter dbProjectID = CreateParameter("projectID", projectID);
                DbParameter dbReleaseID = CreateParameter("releaseID", releaseID);
                DbParameter dbSprintID = CreateParameter("sprintID", sprintID);
                DbParameter dbUserStoryID = CreateParameter("userStoryID", userStoryID);
                DbParameter dbBliID = CreateParameter("bliID", bliID);
                DbParameter dbTaskID = CreateParameter("taskID", taskID);
                connection.Open();

                DbDataReader data = GetGrid(
                    "task/GetAllTasks", 
                    dbProjectID, 
                    dbReleaseID, 
                    dbSprintID, 
                    dbUserStoryID, 
                    dbBliID, 
                    dbTaskID
                );
                while (data.Read())
                {
                    Task task = GetTask(data);
                    tasks.Add(task);
                }
                return tasks;
            }
            finally
            {
                connection.Close();
            }
        }

        public override IList<Task> GetAllTasks(BLI bli, int stateID)
        {
            DbParameter dbID = CreateParameter("bliID", bli.ID);
            DbParameter dbstateID = CreateParameter("stateID", stateID);
            IList<Task> result = new List<Task>();
            try
            {
                connection.Open();
                DbDataReader data = GetGrid("task/GetAllTasksForBLIInTask", dbID, dbstateID);

                while (data.Read())
                {
                    Task task = GetTask(data);

                    result.Add(task);
                }
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Gets the task by ID.
        /// </summary>
        /// <param name="taskID">The task ID.</param>
        /// <returns>Task identified by ID</returns>
        public override Task GetTaskByID(int taskID)
        {
            Task task = null;
            DbParameter dbId = CreateParameter("taskID", taskID);
            try
            {
                connection.Open();
                DbDataReader data = GetVector("task/GetTaskByID", dbId);
                if (!data.Read())
                {
                    connection.Close();
                    throw new Exception("Task NOT found");
                }
                else
                {
                    task = GetTask(data);
                }
            }
            finally
            {
                connection.Close();
            }

            return task;
        }

        /// <summary>
        /// Gets all Tasks for user
        /// </summary>
        /// <param name="userID">ID of user</param>
        /// <returns>All tasks for user</returns>
        public override List<Task> GetAllUsersTasks(int userID)
        {
            DbParameter dbID = CreateParameter("userID", userID);
            List<Task> result = new List<Task>();
            try
            {
                connection.Open();
				DbDataReader data = GetGrid("task/GetAllUsersTasks", dbID);

                while (data.Read())
                {
                    Task task = GetTask(data);

                    result.Add(task);
                }
            }
            finally
            {
                connection.Close();
            }

            return result;
        }        

        /// <summary>
        /// Gets all Tasks for bli
        /// </summary>
        /// <param name="bli">Backlog item</param>
        /// <returns>All tasks in bli with view info</returns>
        public override List<Task> GetAllTasksForBli(int bliID)
        {
            DbParameter dbID = CreateParameter("bliID", bliID);
            List<Task> result = new List<Task>();
            try
            {
                connection.Open();
                DbDataReader data = GetGrid("task/GetAllTasksForBLI", dbID); 

                while (data.Read())
                {
                    Task task = GetTask(data);

                    result.Add(task);
                }
            }
            finally
            {
                connection.Close();
            }
            return result;
        }        

        /// <summary>
        /// Updates the state of the task.
        /// </summary>
        /// <param name="taskID">The task ID.</param>
        /// <param name="state">The state.</param>
        public override void UpdateTaskState(int taskID, string state)
        {
            DbParameter dbtaskID = CreateParameter("taskID", taskID);
            DbParameter dbstate = CreateParameter("taskState", state);

            try
            {
                connection.Open();
                int data = ExecuteNonQuery("task/UpdateTaskState", dbtaskID, dbstate);
                if (data != 1)
                {
                    throw new Exception("Task state NOT Updated!");
                }
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Removes the associated user from the task.
        /// </summary>
        /// <param name="taskID">The task ID.</param>
        /// <param name="userID">The user ID.</param>
        public override void RemoveAssociatedUser(int taskID, int userID)
        {
            DbParameter parTaskID = CreateParameter("taskID", taskID);
            DbParameter parUserID = CreateParameter("userID", userID);

            try
            {
                connection.Open();
                ExecuteNonQuery("task/RemoveAssociatedUser", parTaskID, parUserID);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Adds the associated user to the task.
        /// </summary>
        /// <param name="taskID">The task ID.</param>
        /// <param name="userID">The user ID.</param>
        public override void AddAssociatedUser(int taskID, int userID)
        {
            DbParameter parTaskID = CreateParameter("taskID", taskID);
            DbParameter parUserID = CreateParameter("userID", userID);

            try
            {
                connection.Open();
                ExecuteNonQuery("task/AddAssociatedUser", parTaskID, parUserID);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Gets the users which can be assigned to the task.
        /// </summary>
        /// <param name="taskID">The task ID.</param>
        /// <returns></returns>
        public override IList<User> GetUsersExceptTaskUsers(int taskID)
        {
            List<User> users = new List<User>();
            DbParameter dbtaskID = CreateParameter("taskID", taskID);
            try
            {
                connection.Open();

                DbDataReader data = GetVector("user/GetUsersExceptTaskUsers2", dbtaskID);

                while (data.Read())
                {
                    User user = new User();
                    user.ID = (int)data["ID"];
                    user.Login = (string)data["Login"];
                    user.FullName = (string)data["FullName"];
                    user.Enabled = (bool)data["Enabled"];
                    user.Password = (string)data["Password"];
                    user.Email = (string)data["Email"];

                    users.Add(user);
                }
            }
            finally
            {
                connection.Close();
            }
            return users;
        }
    }
}